package org.tloss.proxyserver.datasource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import org.tloss.proxyserver.ProxyContextEntry;
import org.tloss.proxyserver.ProxyEntry;
import org.tloss.proxyserver.UserInfo;

public class DataSource {
	private javax.sql.DataSource ds = null;

	private DataSource() {

	}

	public static DataSource dataSource = null;

	public static synchronized DataSource getInstanse() {
		if (dataSource == null) {
			dataSource = new DataSource();
			dataSource.init();
		}
		return dataSource;
	}

	public synchronized boolean init() {
		if (ds == null) {
			Context initCtx;
			try {
				initCtx = new InitialContext();
				Context envCtx = (Context) initCtx.lookup("java:comp/env");
				ds = (javax.sql.DataSource) envCtx.lookup("jdbc/ProxyServer");
			} catch (NamingException e) {
			}
		}
		return ds != null;
	}

	public UserInfo getUserInfo(String email, String password) {
		Connection dbConnection = null;
		PreparedStatement statement = null;
		UserInfo userInfo = null;
		String selectTableSQL = "SELECT * FROM users WHERE  email = ? AND password=?";

		try {
			dbConnection = ds.getConnection();
			statement = dbConnection.prepareStatement(selectTableSQL);
			statement.setString(1, email);
			statement.setString(2, password);
			// execute select SQL stetement
			ResultSet rs = statement.executeQuery();
			if (rs.next()) {
				userInfo = new UserInfo();
				userInfo.setEmail(rs.getString("email"));
				userInfo.setPassword(rs.getString("password"));
			}
			return userInfo;

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
			}

			if (dbConnection != null) {
				try {
					dbConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
		return userInfo;
	}

	public boolean updateProxyContextEntryStatus(String context, int newStatus) {
		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;

		String insertTableSQL = "UPDATE proxy_context set status=?  WHERE context=?";

		try {
			dbConnection = ds.getConnection();
			preparedStatement = dbConnection.prepareStatement(insertTableSQL);

			preparedStatement.setInt(1, newStatus);
			preparedStatement.setString(2, context);

			// execute insert SQL stetement
			return preparedStatement.executeUpdate() > 0;

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
			}
			if (dbConnection != null) {
				try {
					dbConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
		return false;
	}

	public boolean updateProxyEntryStatus(String hash, int newStatus) {
		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;

		String insertTableSQL = "UPDATE proxy_entry set status=?  WHERE hash=?";

		try {
			dbConnection = ds.getConnection();
			preparedStatement = dbConnection.prepareStatement(insertTableSQL);

			preparedStatement.setInt(1, newStatus);
			preparedStatement.setString(2, hash);

			// execute insert SQL stetement
			return preparedStatement.executeUpdate() > 0;

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
			}
			if (dbConnection != null) {
				try {
					dbConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
		return false;
	}

	public boolean addProxyEntry(ProxyEntry entry) {
		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;

		String insertTableSQL = "INSERT INTO proxy_entry"
				+ "( protocol,host, port, context,hash,status,proxy_context) VALUES"
				+ "(?,?,?,?,?,?,?)";

		try {
			dbConnection = ds.getConnection();
			preparedStatement = dbConnection.prepareStatement(insertTableSQL);

			preparedStatement.setString(1, entry.getProtocol());
			preparedStatement.setString(2, entry.getHost());
			preparedStatement.setInt(3, entry.getPort());
			preparedStatement.setString(4, entry.getContext());
			preparedStatement.setString(5, entry.getHash());
			preparedStatement.setInt(6, entry.getStatus());
			preparedStatement.setString(7, entry.getProxyContext());
			// execute insert SQL stetement
			return preparedStatement.executeUpdate() > 0;

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
			}
			if (dbConnection != null) {
				try {
					dbConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
		return false;
	}

	public boolean addProxyContextEntry(ProxyContextEntry entry) {
		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;

		String insertTableSQL = "INSERT INTO proxy_context"
				+ "(context,status) VALUES" + "(?,?)";

		try {
			dbConnection = ds.getConnection();
			preparedStatement = dbConnection.prepareStatement(insertTableSQL);

			preparedStatement.setString(1, entry.getContext());
			preparedStatement.setInt(2, entry.getStatus());
			// execute insert SQL stetement
			return preparedStatement.executeUpdate() > 0;

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
			}
			if (dbConnection != null) {
				try {
					dbConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
		return false;
	}

	public List<ProxyContextEntry> getActiveProxyContextEntries() {
		Connection dbConnection = null;
		PreparedStatement statement = null;
		String selectTableSQL = "SELECT * FROM proxy_context WHERE  status <> ?";

		try {
			dbConnection = ds.getConnection();
			statement = dbConnection.prepareStatement(selectTableSQL);
			statement.setInt(1, ProxyContextEntry.DISABLE);

			// execute select SQL stetement
			ResultSet rs = statement.executeQuery();
			List<ProxyContextEntry> list = new ArrayList<ProxyContextEntry>();
			while (rs.next()) {
				ProxyContextEntry entry = new ProxyContextEntry();
				entry.setContext(rs.getString("context"));
				entry.setStatus(rs.getInt("status"));
				list.add(entry);
			}
			return list;

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
			}

			if (dbConnection != null) {
				try {
					dbConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
		return null;

	}

	public List<ProxyEntry> getActiveProxyEntries(String proxyContext) {
		Connection dbConnection = null;
		PreparedStatement statement = null;
		String selectTableSQL = "SELECT * FROM proxy_entry WHERE proxy_context=? AND  status <> ?";

		try {
			dbConnection = ds.getConnection();
			statement = dbConnection.prepareStatement(selectTableSQL);
			statement.setString(1, proxyContext);
			statement.setInt(2, ProxyEntry.DISABLE);

			// execute select SQL stetement
			ResultSet rs = statement.executeQuery();
			List<ProxyEntry> list = new ArrayList<ProxyEntry>();
			while (rs.next()) {
				ProxyEntry entry = new ProxyEntry();
				entry.setProtocol(rs.getString("protocol"));
				entry.setContext(rs.getString("context"));
				entry.setHost(rs.getString("host"));
				entry.setPort(rs.getInt("port"));
				entry.setHash(rs.getString("hash"));
				entry.setStatus(rs.getInt("status"));
				entry.setProxyContext(rs.getString("proxy_context"));
				list.add(entry);
			}
			return list;

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {

					e.printStackTrace();
				}
			}

			if (dbConnection != null) {
				try {
					dbConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
		return null;
	}

}
